<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 5.4.0">


  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">



<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.15.4/css/all.min.css" integrity="sha256-mUZM63G8m73Mcidfrv5E+Y61y7a12O5mW4ezU3bxqW4=" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/animate.css@3.1.1/animate.min.css" integrity="sha256-PR7ttpcvz8qrF57fur/yAx1qXMFJeJFiA6pSzWi0OIE=" crossorigin="anonymous">

<script class="next-config" data-name="main" type="application/json">{"hostname":"zhengmingpei.gitee.io","root":"/","images":"/images","scheme":"Pisces","darkmode":false,"version":"8.8.1","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12},"copycode":false,"bookmark":{"enable":false,"color":"#222","save":"auto"},"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"stickytabs":false,"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"prism":false,"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"}}</script><script src="/js/config.js"></script>
<meta name="description" content="自定义从表中取回的信息The SELECT statement is used to pull information from a table. The general form of the statement is:  SELECT what_to_selectFROM which_tableWHERE conditions_to_satisfy;">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL学习笔记3">
<meta property="og:url" content="https://zhengmingpei.gitee.io/2016/09/27/MySQL-Note3/index.html">
<meta property="og:site_name" content="郑明培的个人博客">
<meta property="og:description" content="自定义从表中取回的信息The SELECT statement is used to pull information from a table. The general form of the statement is:  SELECT what_to_selectFROM which_tableWHERE conditions_to_satisfy;">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="2016-09-27T07:00:00.000Z">
<meta property="article:modified_time" content="2021-11-28T03:26:25.397Z">
<meta property="article:author" content="zhengmingpei">
<meta property="article:tag" content="Mysql">
<meta property="article:tag" content="数据库">
<meta name="twitter:card" content="summary">


<link rel="canonical" href="https://zhengmingpei.gitee.io/2016/09/27/MySQL-Note3/">



<script class="next-config" data-name="page" type="application/json">{"sidebar":"","isHome":false,"isPost":true,"lang":"zh-CN","comments":true,"permalink":"https://zhengmingpei.gitee.io/2016/09/27/MySQL-Note3/","path":"2016/09/27/MySQL-Note3/","title":"MySQL学习笔记3"}</script>

<script class="next-config" data-name="calendar" type="application/json">""</script>
<title>MySQL学习笔记3 | 郑明培的个人博客</title>
  




  <noscript>
    <link rel="stylesheet" href="/css/noscript.css">
  </noscript>
</head>

<body itemscope itemtype="http://schema.org/WebPage" class="use-motion">
  <div class="headband"></div>

  <main class="main">
    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏" role="button">
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <i class="logo-line"></i>
      <h1 class="site-title">郑明培的个人博客</h1>
      <i class="logo-line"></i>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>



<nav class="site-nav">
  <ul class="main-menu menu">
        <li class="menu-item menu-item-home"><a href="/" rel="section"><i class="home fa-fw"></i>首页</a></li>
        <li class="menu-item menu-item-archives"><a href="/archives" rel="section"><i class="archive fa-fw"></i>归档</a></li>
        <li class="menu-item menu-item-about"><a href="/about" rel="section"><i class="user fa-fw"></i>关于</a></li>
        <li class="menu-item menu-item-categories"><a href="/categories" rel="section"><i class="th fa-fw"></i>分类</a></li>
        <li class="menu-item menu-item-tags"><a href="/tags" rel="section"><i class="tags fa-fw"></i>标签</a></li>
  </ul>
</nav>




</div>
        
  
  <div class="toggle sidebar-toggle" role="button">
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
  </div>

  <aside class="sidebar">

    <div class="sidebar-inner sidebar-nav-active sidebar-toc-active">
      <ul class="sidebar-nav">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <div class="sidebar-panel-container">
        <!--noindex-->
        <div class="post-toc-wrap sidebar-panel">
            <div class="post-toc animated"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%87%AA%E5%AE%9A%E4%B9%89%E4%BB%8E%E8%A1%A8%E4%B8%AD%E5%8F%96%E5%9B%9E%E7%9A%84%E4%BF%A1%E6%81%AF"><span class="nav-number">1.</span> <span class="nav-text">自定义从表中取回的信息</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8WHERE%E6%8C%87%E5%AE%9A%E7%AD%9B%E9%80%89%E6%9D%A1%E4%BB%B6"><span class="nav-number">1.1.</span> <span class="nav-text">使用WHERE指定筛选条件</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E9%80%89%E6%8B%A9%E7%89%B9%E5%AE%9A%E8%A1%8C"><span class="nav-number">1.1.1.</span> <span class="nav-text">选择特定行</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E9%80%89%E6%8B%A9%E7%89%B9%E5%AE%9A%E5%88%97"><span class="nav-number">1.1.2.</span> <span class="nav-text">选择特定列</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%8E%92%E5%BA%8F%E7%AD%89%E5%85%B6%E4%BB%96%E9%80%89%E9%A1%B9"><span class="nav-number">1.1.3.</span> <span class="nav-text">排序等其他选项</span></a></li></ol></li></ol></li></ol></div>
        </div>
        <!--/noindex-->

        <div class="site-overview-wrap sidebar-panel">
          <div class="site-author site-overview-item animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">zhengmingpei</p>
  <div class="site-description" itemprop="description"></div>
</div>
<div class="site-state-wrap site-overview-item animated">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
        <a href="/archives">
          <span class="site-state-item-count">36</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
          <a href="/categories">
        <span class="site-state-item-count">4</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
          <a href="/tags">
        <span class="site-state-item-count">41</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>
  <div class="links-of-author site-overview-item animated">
      <span class="links-of-author-item">
        <a href="https://github.com/zhengmingpei" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;zhengmingpei" rel="noopener" target="_blank"><i class="github fa-fw"></i>GitHub</a>
      </span>
      <span class="links-of-author-item">
        <a href="https://gitee.com/zhengmingpei" title="gitee → https:&#x2F;&#x2F;gitee.com&#x2F;zhengmingpei" rel="noopener" target="_blank">gitee</a>
      </span>
      <span class="links-of-author-item">
        <a href="https://space.bilibili.com/32918983" title="bilibili → https:&#x2F;&#x2F;space.bilibili.com&#x2F;32918983" rel="noopener" target="_blank">bilibili</a>
      </span>
  </div>



        </div>
      </div>
    </div>
  </aside>
  <div class="sidebar-dimmer"></div>


    </header>

    
  <div class="back-to-top" role="button" aria-label="返回顶部">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>

<noscript>
  <div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>


    <div class="main-inner post posts-expand">


  


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://zhengmingpei.gitee.io/2016/09/27/MySQL-Note3/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="zhengmingpei">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郑明培的个人博客">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          MySQL学习笔记3
        </h1>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2016-09-27 15:00:00" itemprop="dateCreated datePublished" datetime="2016-09-27T15:00:00+08:00">2016-09-27</time>
    </span>
      <span class="post-meta-item">
        <span class="post-meta-item-icon">
          <i class="far fa-calendar-check"></i>
        </span>
        <span class="post-meta-item-text">更新于</span>
        <time title="修改时间：2021-11-28 11:26:25" itemprop="dateModified" datetime="2021-11-28T11:26:25+08:00">2021-11-28</time>
      </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%9C%A8%E5%8C%97%E4%BA%AC/" itemprop="url" rel="index"><span itemprop="name">在北京</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
        <h2 id="自定义从表中取回的信息"><a href="#自定义从表中取回的信息" class="headerlink" title="自定义从表中取回的信息"></a>自定义从表中取回的信息</h2><p>The <code>SELECT</code> statement is used to pull information from a table. The general form of the statement is: </p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> what_to_select</span><br><span class="line"><span class="keyword">FROM</span> which_table</span><br><span class="line"><span class="keyword">WHERE</span> conditions_to_satisfy;</span><br></pre></td></tr></table></figure>

<span id="more"></span>

<h3 id="使用WHERE指定筛选条件"><a href="#使用WHERE指定筛选条件" class="headerlink" title="使用WHERE指定筛选条件"></a>使用WHERE指定筛选条件</h3><p>官网文档的示例中，有下表pet：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="operator">+</span><span class="comment">----------+--------+---------+------+------------+------------+</span></span><br><span class="line"><span class="operator">|</span> name     <span class="operator">|</span> owner  <span class="operator">|</span> species <span class="operator">|</span> sex  <span class="operator">|</span> birth      <span class="operator">|</span> death      <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">----------+--------+---------+------+------------+------------+</span></span><br><span class="line"><span class="operator">|</span> Fluffy   <span class="operator">|</span> Harold <span class="operator">|</span> cat     <span class="operator">|</span> f    <span class="operator">|</span> <span class="number">1993</span><span class="number">-02</span><span class="number">-04</span> <span class="operator">|</span> <span class="keyword">NULL</span>       <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Claws    <span class="operator">|</span> Gwen   <span class="operator">|</span> cat     <span class="operator">|</span> m    <span class="operator">|</span> <span class="number">1994</span><span class="number">-03</span><span class="number">-17</span> <span class="operator">|</span> <span class="keyword">NULL</span>       <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Buffy    <span class="operator">|</span> Harold <span class="operator">|</span> dog     <span class="operator">|</span> f    <span class="operator">|</span> <span class="number">1989</span><span class="number">-05</span><span class="number">-13</span> <span class="operator">|</span> <span class="keyword">NULL</span>       <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Fang     <span class="operator">|</span> Benny  <span class="operator">|</span> dog     <span class="operator">|</span> m    <span class="operator">|</span> <span class="number">1990</span><span class="number">-08</span><span class="number">-27</span> <span class="operator">|</span> <span class="keyword">NULL</span>       <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Bowser   <span class="operator">|</span> Diane  <span class="operator">|</span> dog     <span class="operator">|</span> m    <span class="operator">|</span> <span class="number">1979</span><span class="number">-08</span><span class="number">-31</span> <span class="operator">|</span> <span class="number">1995</span><span class="number">-07</span><span class="number">-29</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Chirpy   <span class="operator">|</span> Gwen   <span class="operator">|</span> bird    <span class="operator">|</span> f    <span class="operator">|</span> <span class="number">1998</span><span class="number">-09</span><span class="number">-11</span> <span class="operator">|</span> <span class="keyword">NULL</span>       <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Whistler <span class="operator">|</span> Gwen   <span class="operator">|</span> bird    <span class="operator">|</span> <span class="keyword">NULL</span> <span class="operator">|</span> <span class="number">1997</span><span class="number">-12</span><span class="number">-09</span> <span class="operator">|</span> <span class="keyword">NULL</span>       <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Slim     <span class="operator">|</span> Benny  <span class="operator">|</span> snake   <span class="operator">|</span> m    <span class="operator">|</span> <span class="number">1996</span><span class="number">-04</span><span class="number">-29</span> <span class="operator">|</span> <span class="keyword">NULL</span>       <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Puffball <span class="operator">|</span> Diane  <span class="operator">|</span> hamster <span class="operator">|</span> f    <span class="operator">|</span> <span class="number">1999</span><span class="number">-03</span><span class="number">-30</span> <span class="operator">|</span> <span class="keyword">NULL</span>       <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">----------+--------+---------+------+------------+------------+</span></span><br></pre></td></tr></table></figure>

<h4 id="选择特定行"><a href="#选择特定行" class="headerlink" title="选择特定行"></a>选择特定行</h4><p>You can select only particular rows from your table. For example, if you want to verify the change that you made to Bowser’s birth date, select Bowser’s record like this: </p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> pet <span class="keyword">WHERE</span> name <span class="operator">=</span> <span class="string">&#x27;Bowser&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>输出为：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="operator">+</span><span class="comment">--------+-------+---------+------+------------+------------+</span></span><br><span class="line"><span class="operator">|</span> name   <span class="operator">|</span> owner <span class="operator">|</span> species <span class="operator">|</span> sex  <span class="operator">|</span> birth      <span class="operator">|</span> death      <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------+-------+---------+------+------------+------------+</span></span><br><span class="line"><span class="operator">|</span> Bowser <span class="operator">|</span> Diane <span class="operator">|</span> dog     <span class="operator">|</span> m    <span class="operator">|</span> <span class="number">1989</span><span class="number">-08</span><span class="number">-31</span> <span class="operator">|</span> <span class="number">1995</span><span class="number">-07</span><span class="number">-29</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------+-------+---------+------+------------+------------+</span></span><br></pre></td></tr></table></figure>

<p>更复杂的示例使用了逻辑运算符：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> pet <span class="keyword">WHERE</span> (species <span class="operator">=</span> <span class="string">&#x27;cat&#x27;</span> <span class="keyword">AND</span> sex <span class="operator">=</span> <span class="string">&#x27;m&#x27;</span>)</span><br><span class="line">    <span class="operator">-</span><span class="operator">&gt;</span> <span class="keyword">OR</span> (species <span class="operator">=</span> <span class="string">&#x27;dog&#x27;</span> <span class="keyword">AND</span> sex <span class="operator">=</span> <span class="string">&#x27;f&#x27;</span>);</span><br><span class="line"><span class="operator">+</span><span class="comment">-------+--------+---------+------+------------+-------+</span></span><br><span class="line"><span class="operator">|</span> name  <span class="operator">|</span> owner  <span class="operator">|</span> species <span class="operator">|</span> sex  <span class="operator">|</span> birth      <span class="operator">|</span> death <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-------+--------+---------+------+------------+-------+</span></span><br><span class="line"><span class="operator">|</span> Claws <span class="operator">|</span> Gwen   <span class="operator">|</span> cat     <span class="operator">|</span> m    <span class="operator">|</span> <span class="number">1994</span><span class="number">-03</span><span class="number">-17</span> <span class="operator">|</span> <span class="keyword">NULL</span>  <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Buffy <span class="operator">|</span> Harold <span class="operator">|</span> dog     <span class="operator">|</span> f    <span class="operator">|</span> <span class="number">1989</span><span class="number">-05</span><span class="number">-13</span> <span class="operator">|</span> <span class="keyword">NULL</span>  <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-------+--------+---------+------+------------+-------+</span></span><br></pre></td></tr></table></figure>

<p>还有的指定日期范围的，比如：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> pet <span class="keyword">WHERE</span> birth <span class="operator">&gt;=</span> <span class="string">&#x27;1998-1-1&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h4 id="选择特定列"><a href="#选择特定列" class="headerlink" title="选择特定列"></a>选择特定列</h4><p>比如，只选择某个栏目：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> owner <span class="keyword">FROM</span> pet;</span><br><span class="line"><span class="operator">+</span><span class="comment">--------+</span></span><br><span class="line"><span class="operator">|</span> owner  <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------+</span></span><br><span class="line"><span class="operator">|</span> Harold <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Gwen   <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Harold <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Benny  <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Diane  <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Gwen   <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Gwen   <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Benny  <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Diane  <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------+</span></span><br></pre></td></tr></table></figure>

<p>为了使重复的项目只出现一次，可以使用<code>DISTINCT</code>关键字：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> owner <span class="keyword">FROM</span> pet;</span><br></pre></td></tr></table></figure>


<h4 id="排序等其他选项"><a href="#排序等其他选项" class="headerlink" title="排序等其他选项"></a>排序等其他选项</h4><ul>
<li>使用<code>ORDER BY</code>进行排序。例如：</li>
</ul>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name, birth <span class="keyword">FROM</span> pet <span class="keyword">ORDER</span> <span class="keyword">BY</span> birth;</span><br></pre></td></tr></table></figure>

<p>默认升序排列，可以使用<code>DESC</code>降序排列，比如：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name, birth <span class="keyword">FROM</span> pet <span class="keyword">ORDER</span> <span class="keyword">BY</span> birth <span class="keyword">DESC</span>;</span><br></pre></td></tr></table></figure>

<p>根据多个栏目排序，比如：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name, species, birth <span class="keyword">FROM</span> pet</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> species, birth <span class="keyword">DESC</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>日期差值计算</li>
</ul>
<p>使用<code>TIMESTAMPDIFF()</code>函数进行日期差值计算，其中，<code>CURDATE()</code>函数代入当前日期。</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name, birth, CURDATE(),</span><br><span class="line">    <span class="operator">-</span><span class="operator">&gt;</span> TIMESTAMPDIFF(<span class="keyword">YEAR</span>,birth,CURDATE()) <span class="keyword">AS</span> age</span><br><span class="line">    <span class="operator">-</span><span class="operator">&gt;</span> <span class="keyword">FROM</span> pet;</span><br><span class="line"><span class="operator">+</span><span class="comment">----------+------------+------------+------+</span></span><br><span class="line"><span class="operator">|</span> name     <span class="operator">|</span> birth      <span class="operator">|</span> CURDATE()  <span class="operator">|</span> age  <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">----------+------------+------------+------+</span></span><br><span class="line"><span class="operator">|</span> Fluffy   <span class="operator">|</span> <span class="number">1993</span><span class="number">-02</span><span class="number">-04</span> <span class="operator">|</span> <span class="number">2003</span><span class="number">-08</span><span class="number">-19</span> <span class="operator">|</span>   <span class="number">10</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Claws    <span class="operator">|</span> <span class="number">1994</span><span class="number">-03</span><span class="number">-17</span> <span class="operator">|</span> <span class="number">2003</span><span class="number">-08</span><span class="number">-19</span> <span class="operator">|</span>    <span class="number">9</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Buffy    <span class="operator">|</span> <span class="number">1989</span><span class="number">-05</span><span class="number">-13</span> <span class="operator">|</span> <span class="number">2003</span><span class="number">-08</span><span class="number">-19</span> <span class="operator">|</span>   <span class="number">14</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Fang     <span class="operator">|</span> <span class="number">1990</span><span class="number">-08</span><span class="number">-27</span> <span class="operator">|</span> <span class="number">2003</span><span class="number">-08</span><span class="number">-19</span> <span class="operator">|</span>   <span class="number">12</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Bowser   <span class="operator">|</span> <span class="number">1989</span><span class="number">-08</span><span class="number">-31</span> <span class="operator">|</span> <span class="number">2003</span><span class="number">-08</span><span class="number">-19</span> <span class="operator">|</span>   <span class="number">13</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Chirpy   <span class="operator">|</span> <span class="number">1998</span><span class="number">-09</span><span class="number">-11</span> <span class="operator">|</span> <span class="number">2003</span><span class="number">-08</span><span class="number">-19</span> <span class="operator">|</span>    <span class="number">4</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Whistler <span class="operator">|</span> <span class="number">1997</span><span class="number">-12</span><span class="number">-09</span> <span class="operator">|</span> <span class="number">2003</span><span class="number">-08</span><span class="number">-19</span> <span class="operator">|</span>    <span class="number">5</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Slim     <span class="operator">|</span> <span class="number">1996</span><span class="number">-04</span><span class="number">-29</span> <span class="operator">|</span> <span class="number">2003</span><span class="number">-08</span><span class="number">-19</span> <span class="operator">|</span>    <span class="number">7</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Puffball <span class="operator">|</span> <span class="number">1999</span><span class="number">-03</span><span class="number">-30</span> <span class="operator">|</span> <span class="number">2003</span><span class="number">-08</span><span class="number">-19</span> <span class="operator">|</span>    <span class="number">4</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">----------+------------+------------+------+</span></span><br></pre></td></tr></table></figure>

<p>使用<code>IS NOT NULL</code>，只计算已经死亡宠物的年龄：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name, birth, death,</span><br><span class="line">    <span class="operator">-</span><span class="operator">&gt;</span> TIMESTAMPDIFF(<span class="keyword">YEAR</span>,birth,death) <span class="keyword">AS</span> age</span><br><span class="line">    <span class="operator">-</span><span class="operator">&gt;</span> <span class="keyword">FROM</span> pet <span class="keyword">WHERE</span> death <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">ORDER</span> <span class="keyword">BY</span> age;</span><br></pre></td></tr></table></figure>

<p>假设当前月份是4月，查找下个月生日的宠物：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name, birth <span class="keyword">FROM</span> pet <span class="keyword">WHERE</span> <span class="keyword">MONTH</span>(birth) <span class="operator">=</span> <span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<p>计算日期差值的复杂例子：</p>
<p>假设当前月份是12月，你想查一下在下个月生日的宠物，使用<code>WHERE MONTH(birth) =13</code>进行筛选是不可行的，可以使用：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name, birth <span class="keyword">FROM</span> pet</span><br><span class="line">    <span class="operator">-</span><span class="operator">&gt;</span> <span class="keyword">WHERE</span> <span class="keyword">MONTH</span>(birth) <span class="operator">=</span> <span class="keyword">MONTH</span>(DATE_ADD(CURDATE(),<span class="type">INTERVAL</span> <span class="number">1</span> <span class="keyword">MONTH</span>));</span><br></pre></td></tr></table></figure>

<p>另一种实现的方法是利用了余数：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name, birth <span class="keyword">FROM</span> pet</span><br><span class="line">    <span class="operator">-</span><span class="operator">&gt;</span> <span class="keyword">WHERE</span> <span class="keyword">MONTH</span>(birth) <span class="operator">=</span> <span class="built_in">MOD</span>(<span class="keyword">MONTH</span>(CURDATE()), <span class="number">12</span>) <span class="operator">+</span> <span class="number">1</span>;</span><br></pre></td></tr></table></figure>



    </div>

    
    
    

    <footer class="post-footer">
          <div class="post-tags">
              <a href="/tags/Mysql/" rel="tag"># Mysql</a>
              <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="tag"># 数据库</a>
          </div>

        

          <div class="post-nav">
            <div class="post-nav-item">
                <a href="/2016/09/22/LinuxShellScriptingCookbook2/" rel="prev" title="LinuxShellScriptingCookbook2">
                  <i class="fa fa-chevron-left"></i> LinuxShellScriptingCookbook2
                </a>
            </div>
            <div class="post-nav-item">
                <a href="/2016/09/28/MySQL-Note4/" rel="next" title="MySQL学习笔记4">
                  MySQL学习笔记4 <i class="fa fa-chevron-right"></i>
                </a>
            </div>
          </div>
    </footer>
  </article>
</div>






</div>
  </main>

  <footer class="footer">
    <div class="footer-inner">


<div class="copyright">
  &copy; 
  <span itemprop="copyrightYear">2021</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">zhengmingpei</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.js.org/pisces/" rel="noopener" target="_blank">NexT.Pisces</a> 强力驱动
  </div>

    </div>
  </footer>

  
  <script src="https://cdn.jsdelivr.net/npm/animejs@3.2.1/lib/anime.min.js" integrity="sha256-XL2inqUJaslATFnHdJOi9GfQ60on8Wx1C2H8DYiN1xY=" crossorigin="anonymous"></script>
<script src="/js/comments.js"></script><script src="/js/utils.js"></script><script src="/js/motion.js"></script><script src="/js/next-boot.js"></script>

  





  





</body>
</html>
